﻿//------------------------------------------------------------------------------
// 创建标识: Copyright (C) 2008 Socansoft.com 版权所有
// 创建描述: SocanCode代码生成器自动创建于 2008-8-13 17:43:17
//
// 功能描述: 
//
// 修改标识: 
// 修改描述: 
//------------------------------------------------------------------------------

using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using IDAL;
using DBUtility;
using System.Collections;
using System.Collections.Generic;


namespace SQLServerDAL
{
    /// <summary>
    /// 数据访问类 USERS
    /// </summary>
    public class USERS : IUSERS
    {
        public USERS()
        { }

        #region  成员方法
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public string Add(Model.USERS model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into [USERS](");
            strSql.Append("[USERS_CODE],[USERS_NAME],[USERS_PASSWORD],[USERS_FIELD1],[USERS_FIELD2])");
            strSql.Append(" values (");
            strSql.Append("@USERS_CODE,@USERS_NAME,@USERS_PASSWORD,@USERS_FIELD1,@USERS_FIELD2)");
            SqlParameter[] parameters = {
                    new SqlParameter("@USERS_CODE", SqlDbType.VarChar,20),
                    new SqlParameter("@USERS_NAME", SqlDbType.VarChar,100),
                    new SqlParameter("@USERS_PASSWORD", SqlDbType.VarChar,100),
                    new SqlParameter("@USERS_FIELD1", SqlDbType.VarChar,20),
                    new SqlParameter("@USERS_FIELD2", SqlDbType.VarChar,20)
            };
            parameters[0].Value = model.USERS_CODE;
            parameters[1].Value = model.USERS_NAME;
            parameters[2].Value = model.USERS_PASSWORD;

            if (model.USERS_FIELD1 != null)
                parameters[3].Value = model.USERS_FIELD1;
            else
                parameters[3].Value = DBNull.Value;


            if (model.USERS_FIELD2 != null)
                parameters[4].Value = model.USERS_FIELD2;
            else
                parameters[4].Value = DBNull.Value;


            if (SqlHelper.ExecuteSql(SqlHelper.LocalSqlServer, strSql.ToString(), parameters) > 0)
                   return model.USERS_CODE;
            else
                return null;
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Model.USERS model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update [USERS] set ");
            strSql.Append("[USERS_NAME]=@USERS_NAME,");
            //strSql.Append("[USERS_PASSWORD]=@USERS_PASSWORD,");
            strSql.Append("[VIPCO]=@VIPCO,");
            strSql.Append("[USERS_DEFAULT]=@USERS_DEFAULT,");
            strSql.Append("[USERS_STOP]=@USERS_STOP,");
            strSql.Append("[USERS_FIELD1]=@USERS_FIELD1,");
            strSql.Append("[USERS_FIELD2]=@USERS_FIELD2");
            strSql.Append(" where [USERS_CODE]=@USERS_CODE");
            SqlParameter[] parameters = {
                    new SqlParameter("@USERS_CODE", SqlDbType.VarChar,20),
                    new SqlParameter("@USERS_NAME", SqlDbType.VarChar,100),
                    new SqlParameter("@USERS_PASSWORD", SqlDbType.VarChar,100),
                    new SqlParameter("@VIPCO", SqlDbType.SmallInt),
                    new SqlParameter("@USERS_DEFAULT", SqlDbType.SmallInt),
                    new SqlParameter("@USERS_STOP", SqlDbType.SmallInt),
                    new SqlParameter("@USERS_FIELD1", SqlDbType.VarChar,20),
                    new SqlParameter("@USERS_FIELD2", SqlDbType.VarChar,20)
            };
            parameters[0].Value = model.USERS_CODE;
            parameters[1].Value = model.USERS_NAME;
            parameters[2].Value = model.USERS_PASSWORD;
            parameters[3].Value = model.VIPCO;
            parameters[4].Value = model.USERS_DEFAULT;
            parameters[5].Value = model.USERS_STOP;

            if (model.USERS_FIELD1 != null)
                parameters[6].Value = model.USERS_FIELD1;
            else
                parameters[6].Value = DBNull.Value;


            if (model.USERS_FIELD2 != null)
                parameters[7].Value = model.USERS_FIELD2;
            else
                parameters[7].Value = DBNull.Value;


            return SqlHelper.ExecuteSql(SqlHelper.LocalSqlServer, strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(string USERS_CODE)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete [USERS] ");
            strSql.Append(" where [USERS_CODE]=@USERS_CODE");
            strSql.Append(" delete USER_USERGROUP where [UUG_USERCODE]=@USERS_CODE");
            SqlParameter[] parameters = {
                    new SqlParameter("@USERS_CODE", SqlDbType.VarChar,20)};
            parameters[0].Value = USERS_CODE;

            return SqlHelper.ExecuteSql(SqlHelper.LocalSqlServer, strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string USERS_CODE)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from [USERS]");
            strSql.Append(" where [USERS_CODE]= @USERS_CODE");
            SqlParameter[] parameters = {
                    new SqlParameter("@USERS_CODE", SqlDbType.VarChar,20)};
            parameters[0].Value = USERS_CODE;
            return SqlHelper.Exists(SqlHelper.LocalSqlServer, strSql.ToString(), parameters);
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Model.USERS GetModel(string USERS_CODE)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from [USERS] ");
            strSql.Append(" where [USERS_CODE]=@USERS_CODE");
            SqlParameter[] parameters = {
                    new SqlParameter("@USERS_CODE", SqlDbType.VarChar,20)};
            parameters[0].Value = USERS_CODE;
            DataSet ds = SqlHelper.Query(SqlHelper.LocalSqlServer, strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow r = ds.Tables[0].Rows[0];
                return GetModel(r);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获取数据集,建议只在多表联查时使用
        /// </summary>
        public DataSet GetDataSet(string strSql)
        {
            return SqlHelper.Query(SqlHelper.LocalSqlServer, strSql);
        }

        /// <summary>
        /// 获取泛型数据列表,建议只在多表联查时使用
        /// </summary>
        public IList<Model.USERS> GetList(string strSql)
        {
            return GetList(GetDataSet(strSql));
        }

        /// <summary>
        /// 获取数据集,在单表查询时使用
        /// </summary>
        public DataSet GetDataSet(string strWhat, string strWhere, string strOrderby)
        {
            if (string.IsNullOrEmpty(strWhat))
                strWhat = "*";
            StringBuilder strSql = new StringBuilder("select " + strWhat + " from [USERS]");
            if (!string.IsNullOrEmpty(strWhere))
                strSql.Append(" where " + strWhere);
            if (!string.IsNullOrEmpty(strOrderby))
                strSql.Append(" order by " + strOrderby);
            return SqlHelper.Query(SqlHelper.LocalSqlServer, strSql.ToString());
        }

        /// <summary>
        /// 获取泛型数据列表,在单表查询时使用
        /// </summary>
        public IList<Model.USERS> GetList(string strWhat, string strWhere, string strOrderby)
        {
            return GetList(GetDataSet(strWhat, strWhere, strOrderby));
        }

        /// <summary>
        /// 分页获取泛型数据列表
        /// </summary>
        public IList<Model.USERS> GetList(int pageSize, int pageIndex, string fldSort, bool sort, string strCondition, out int pageCount, out int count)
        {
            string strSql;
            DataSet ds = SqlHelper.PageList(SqlHelper.LocalSqlServer, "[USERS]", null, pageSize, pageIndex, fldSort, sort, strCondition, "USERS_CODE", false, out pageCount, out count, out strSql);
            return GetList(ds);
        }
        #endregion

        /// <summary>
        /// 由一行数据得到一个实体
        /// </summary>
        private Model.USERS GetModel(DataRow r)
        {
            Model.USERS model = new Model.USERS();
            model.USERS_CODE = SqlHelper.GetString(r["USERS_CODE"]);
            model.USERS_NAME = SqlHelper.GetString(r["USERS_NAME"]);
            model.USERS_PASSWORD = SqlHelper.GetString(r["USERS_PASSWORD"]);
            model.VIPCO = SqlHelper.GetInt(r["VIPCO"]);
            model.USERS_DEFAULT = SqlHelper.GetInt(r["USERS_DEFAULT"]);
            model.USERS_STOP = SqlHelper.GetInt(r["USERS_STOP"]);
            model.USERS_FIELD1 = SqlHelper.GetString(r["USERS_FIELD1"]);
            model.USERS_FIELD2 = SqlHelper.GetString(r["USERS_FIELD2"]);
            return model;
        }

        /// <summary>
        /// 由数据集得到泛型数据列表
        /// </summary>
        private IList<Model.USERS> GetList(DataSet ds)
        {
            List<Model.USERS> l = new List<Model.USERS>();
            foreach (DataRow r in ds.Tables[0].Rows)
            {
                l.Add(GetModel(r));
            }
            return l;
        }




        /// <summary>
        /// 判断登录权限
        /// </summary>
        public DataRow CheckLogin(string USERS_CODE,string pwd)
        {
            //StringBuilder strSql = new StringBuilder();
            //strSql.Append("select * from [USERS] ");
            //strSql.Append(" where [USERS_CODE]=@USERS_CODE");
            SqlParameter[] parameters = {
                    new SqlParameter("@USERS_CODE", SqlDbType.VarChar,20),
                    new SqlParameter("@Pwd", SqlDbType.VarChar,100)};
            parameters[0].Value = USERS_CODE;
            parameters[1].Value = pwd;
            DataSet ds = SqlHelper.RunProcedure(SqlHelper.LocalSqlServer, "P_User_Login", parameters,"ds");
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow r = ds.Tables[0].Rows[0];
                return r;
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 获得权限列表及操作数据
        /// </summary>
        public DataSet GetModuleList(string usercode, string flag)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@UserCode", SqlDbType.VarChar,20),
                    new SqlParameter("@Flag", SqlDbType.VarChar,20)};
            parameters[0].Value = usercode;
            parameters[1].Value = flag;
            return SqlHelper.RunProcedure(SqlHelper.LocalSqlServer, "P_User_Function_List", parameters, "ds");
        }

        /// <summary>
        /// 系统 功能/操作 List
        /// </summary>
        public DataSet P_System_Functions_Operate_List()
        {
            SqlParameter[] parameters = {};
            return SqlHelper.RunProcedure(SqlHelper.LocalSqlServer, "P_System_Functions_Operate_List", parameters, "ds");
        }

        /// <summary>
        /// 用户组权限设置
        /// </summary>
        public bool P_UserGroup_Function_Operate_Update(string ugcode, string funccode, string operatecode, int tmp)
        {
            SqlParameter[] p =
                {
                    SqlHelper.MakeInParam("@UGCODE",SqlDbType.VarChar,20,ugcode), 
                    SqlHelper.MakeInParam("@FUNCCODE",SqlDbType.VarChar,15,funccode),
                    SqlHelper.MakeInParam("@OPERATECODE",SqlDbType.VarChar,15,operatecode),
                    SqlHelper.MakeInParam("@P",SqlDbType.Int,4,tmp),                    
                };
            return ((int)SqlHelper.RunProcedureExecuteNonQuery(SqlHelper.LocalSqlServer, "P_UserGroup_Function_Operate_Update", p)>0);           
        }

        public bool UpdateUserPassword(string userId, string newPassword)
        {
            SqlParameter[] p = 
                {
                    SqlHelper.MakeInParam("@userId",SqlDbType.VarChar,30,userId),
                    SqlHelper.MakeInParam("@newPassword",SqlDbType.VarChar,200,newPassword),
                    SqlHelper.MakeInParam("@rowCount",SqlDbType.Int,4)
                };
            p[2].Direction = ParameterDirection.Output;
            SqlHelper.RunProcedure(SqlHelper.LocalSqlServer, "UpdateUserPassword", p, "tbl");
            return Convert.ToInt32(p[2].Value) == 1;
        }


        /// <summary>
        /// 获取 角色组成员
        /// </summary>
        public DataSet P_User_GetGroupUserByCode(string ugcode)
        {
            SqlParameter[] parameters = { new SqlParameter("@GroupCode", SqlDbType.VarChar, 20) };
            parameters[0].Value = ugcode;
            return SqlHelper.RunProcedure(SqlHelper.LocalSqlServer, "P_User_GetGroupUserByCode", parameters, "ds");
        }
        /// <summary>
        /// 功能：返回与传入操作员在同一个组的操作员
        /// </summary>
        /// <param name="admin">操作员ID</param>
        /// <param name="iStop">是否启用标志-1为全部,0有效的，1无效的</param>
        /// <returns></returns>
        public DataSet getSameGroupManager(string admin, int iStop)
        {
            SqlParameter[] parameters = 
                                       { 
                                           new SqlParameter("@Admin", SqlDbType.VarChar, 50)  ,       
                                           new SqlParameter("@iStop", SqlDbType.Int, 4)
                                       };
            parameters[0].Value = admin;
            parameters[1].Value = iStop;
            return SqlHelper.RunProcedure(SqlHelper.LocalSqlServer, "sp_getSameGroupManager", parameters, "ds");
        }
    }
}
